package com.atguigu.member.dao

import org.apache.spark.sql.SparkSession

/**
 * description ：查询 dws 数据
 * author      ：剧情再美终是戏 
 * mail        : 13286520398@163.com
 * date        ：Created in 2020/3/6 15:52
 * modified By ：
 * version:    : 1.0
 */
object DwsMemberDao {
  /**
   * 统计各memberlevel等级 支付金额 top 3 的用户
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:04
   * @param spark sparkSession
   * @param dn    分区表时间
   * @param n     topN
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryTopNUserByWebsiteAndMemeberLevel(spark: SparkSession, dt: String, dn: String, n: Int) = {
    val sql =
      s"""
         |select
         |  *
         |from (
         |    select
         |        *, row_number() over(partition by website, memberlevel order by caset(paymoney as decimal(10,4))) rownum
         |    from dws.dws_member
         |    ${handledtdn(dt, dn)}
         |    )
         |where rownum < 4
         |""".stripMargin
  }

  /**
   * 统计各vip等级人数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:04
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryLevelCountByVipName(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   vip_level, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by vip_level
         |""".stripMargin
  }

  /**
   * 统计各用户级别（memberlevel）的用户数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:03
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryUserCountByLevel(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   memberlevel, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by memberlevel
         |""".stripMargin
  }

  /**
   * 统计通过各广告跳转（adname）的用户数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:03
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryUserCountByAdname(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   adname, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by adname
         |""".stripMargin
  }

  private def handledtdn(dt: String, dn: String): String = {
    val buffer = new StringBuffer("where 1 = 1")
    if (null != dt && dt.length > 0) {
      buffer.append(" and dt = " + dt)
    }
    if (null != dn && dn.length > 0) {
      buffer.append(" and dn = " + dn)
    }
    buffer.toString;
  }

  /**
   * 统计各所属平台的（regsourcename）用户数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:02
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryUserCountByRegsourceName(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   regsourcename, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by regsourcename
         |""".stripMargin
  }

  /**
   * 统计各所属网站（sitename）的用户数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:02
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryUserCountBySiteName(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   sitename, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by sitename
         |""".stripMargin
  }

  /**
   * 统计通过各注册跳转地址(appregurl)进行注册的用户数
   *
   * @Author 剧情再美终是戏
   * @Date 2020/3/6 16:02
   * @param spark sparkSession
   * @param dn    分区表时间
   * @return java.lang.Object
   * @Version 1.0
   **/
  def queryRegsByAppregurl(spark: SparkSession, dt: String, dn: String) = {
    val sql =
      s"""
         |select
         |   appregurl, count(1)
         |from dws.dws_member
         |${handledtdn(dt, dn)}
         |group by appregurl
         |""".stripMargin
  }

}
